//
//  YHY_DataBaseTool.m
//  Travels
//
//  Created by yanghuiyan on 16/3/17.
//  Copyright © 2016年 yanghuiyan. All rights reserved.
//

#import "YHY_DataBaseTool.h"
#import "MVUrlModel.h"
#import <sqlite3.h>


// 延展
@interface YHY_DataBaseTool ()

// 数据库存储路径
@property (nonatomic, strong) NSString *dataBasePath;

@end

//定义全局变量 － 为了只保证存在一个DataBaseTool对象
//  static(静态的)
static YHY_DataBaseTool *dataBaseTool = nil;


@implementation YHY_DataBaseTool

#pragma mark - 获取dataBaseTool对象

+ (instancetype)shareDataBase {
    //    保证整个工程在任何时候获取的dataBaseTool对象相同
    
    //    单例设计模式
    if (!dataBaseTool) {
        dataBaseTool = [[YHY_DataBaseTool alloc] init];
        [dataBaseTool openDataBase];
        [dataBaseTool createDetailModelTable];
    }
    return dataBaseTool;
}



/********************  HOME  SQLITE  ********************/

#pragma mark - 创建并开启数据库

#pragma mark - 打开数据库

// 定义一个数据库指针
static sqlite3 *db = nil;

- (void)openDataBase {
    if (db != nil) {
        //        NSLog(@"数据库打开成功");
    }
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filePath = [documentPath stringByAppendingPathComponent:@"TTMusicMV.sqlite"];
        NSLog(@"filePath = %@",filePath);
    BOOL result = sqlite3_open(filePath.UTF8String, &db);
    if (result == SQLITE_OK) {
                NSLog(@"打开成功");
    }else{
                NSLog(@"打开失败");
    }
    
}



#pragma mark - 关闭数据库

- (void)closeDataBase {
    
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    } else {
        NSLog(@"数据库关闭失败");
    }
}


/********************  TTMusicMV  ********************/

#pragma mark - 建表
- (void)createDetailModelTable {
    
    // 创建sql语句
   
    // 1.准备sql语句
    NSString *sql = @"create table if not exists CPYMVModelTable(url text not null, desc text not null, bigPicUrl text not null)";
    // TEXT 代表字符串
   
    // 2.执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"CPYMVModelTable创建成功");
    }else{
        
        NSLog(@"CPYMVModelTable创建失败");
    }
}

#pragma mark - 插入

- (void)insertCPYMVModelIntoTable:(CPYMVModel *)mvModel {
    
    MVUrlModel *model = mvModel.mvList[0];
    
    NSString *sql = [NSString stringWithFormat:@"insert into CPYMVModelTable(url, desc, bigPicUrl) values ('%@', '%@', '%@')", model.url, mvModel.desc, mvModel.bigPicUrl];
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"CPYMVModelTable插入成功");
    }else{
        
        NSLog(@"CPYMVModelTable插入失败");
    }
}

#pragma mark - 查询所有
- (NSMutableArray *)selectCPYMVModelTable {
    
    NSMutableArray *array = [NSMutableArray array];
    
    //     查询所有
    NSString *sql = @"select * from CPYMVModelTable";
    
    // 创建数据库跟随指针, 主要是用于遍历查询表中每一行上的数据
    sqlite3_stmt *stmt = nil;
    
    // 准备查询工作---预执行
    
    
    
    // 参数3. -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"CPYMVModelTable查询准备成功");
    }else{
        
        NSLog(@"CPYMVModelTable查询准备失败");
        return nil;
    }
    
    // 循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char * charUrl = sqlite3_column_text(stmt, 0);
        const unsigned char * charDesc = sqlite3_column_text(stmt, 1);
        const unsigned char * charBigPicUrl = sqlite3_column_text(stmt, 2);
        
        NSString *url = [NSString stringWithUTF8String:(const char *) charUrl];
        NSString *desc = [NSString stringWithUTF8String:(const char *) charDesc];
        NSString *bigPicUrl = [NSString stringWithUTF8String:(const char *) charBigPicUrl];
        
        CPYMVModel *mvModel = [[CPYMVModel alloc] init];
        mvModel.bigPicUrl = bigPicUrl;
        mvModel.desc = desc;
        mvModel.url = url;
        
        [array addObject:mvModel];
//        [array addObject:model];
        
    }
    return array;
}

#pragma mark - 删除
- (void)deleteCPYMVModelTable:(NSString *)bigPicUrl {
    
    NSString *sql = [NSString stringWithFormat:@"delete from CPYMVModelTable where bigPicUrl = '%@'", bigPicUrl];
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"CPYMVModelTable删除成功");
    }else{
        
        NSLog(@"CPYMVModelTable删除失败");
    }
}

#pragma mark - 删除表

- (void)dropCPYMVModelTable {
    NSString *sql = @"drop table CPYMVModelTable";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"CPYMVModelTable删除表成功");
    }else{
        
        NSLog(@"CPYMVModelTable删除表失败");
    }

}

#pragma mark - 判断是否存有此页数据

- (BOOL)selectDataBase:(NSString *)bigPicUrl{
    
    //     查询
    NSString *sql = [NSString stringWithFormat:@"select * from CPYMVModelTable where bigPicUrl = '%@'",bigPicUrl];
    // 创建数据库跟随指针, 主要是用于遍历查询表中每一行上的数据
    sqlite3_stmt *stmt = nil;
    
    // 准备查询工作---预执行
    // 参数3. -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询表格成功");
        
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            sqlite3_finalize(stmt);
            NSLog(@"已在表中");
            return YES;
        }
        sqlite3_finalize(stmt);
        NSLog(@"没有在表中");
        return NO;
    }else{
        sqlite3_finalize(stmt);
        NSLog(@"查询表格失败啊!");
        return NO;
    }
    
}

@end
